CREATE PROCEDURE Subject_FindAll
AS
BEGIN
    Select 
    SubjectId,
    CourseId,
    Name,
    Description,
    Body,
    Picture,
    CreatedById,
    CreatedBy,
    ReplyTo,
    IsActive,
    SortIndex,
    FileAttachmentId,
    DateCreated,
    IsDelete
    from Subject
END
GO

CREATE PROCEDURE Subject_FindById
@SubjectId int
AS
BEGIN
    Select * from Subject where SubjectId = @SubjectId
END
GO

CREATE PROCEDURE Subject_FindByName
@name nvarchar(50)
AS
BEGIN
    Select * from Subject where SubjectId = @name
END
GO

CREATE PROCEDURE Subject_Insert
        @SubjectId int ,
        @CourseId int ,
        @Name nvarchar (250),
        @Description nvarchar (250),
        @Body ntext (1073741823),
        @Picture nvarchar (250),
        @CreatedById int ,
        @CreatedBy nvarchar (50),
        @ReplyTo int ,
        @IsActive bit ,
        @SortIndex int ,
        @FileAttachmentId int ,
        @DateCreated datetime ,
        @IsDelete bit 
AS
BEGIN
INSERT INTO Subject
(
        SubjectId,
        CourseId,
        Name,
        Description,
        Body,
        Picture,
        CreatedById,
        CreatedBy,
        ReplyTo,
        IsActive,
        SortIndex,
        FileAttachmentId,
        DateCreated,
        IsDelete
)
VALUES
(
        @SubjectId,
        @CourseId,
        @Name,
        @Description,
        @Body,
        @Picture,
        @CreatedById,
        @CreatedBy,
        @ReplyTo,
        @IsActive,
        @SortIndex,
        @FileAttachmentId,
        @DateCreated,
        @IsDelete
)
select @SubjectId = @@IDENTITY
END
GO

CREATE PROCEDURE Subject_Update
        @SubjectId int ,
        @CourseId int ,
        @Name nvarchar (250),  
        @Description nvarchar (250),  
        @Body ntext (1073741823),  
        @Picture nvarchar (250),  
        @CreatedById int ,
        @CreatedBy nvarchar (50),  
        @ReplyTo int ,
        @IsActive bit ,
        @SortIndex int ,
        @FileAttachmentId int ,
        @DateCreated datetime ,
        @IsDelete bit
AS
BEGIN
UPDATE  Subject SET
    CourseId = @CourseId,
    Name = @Name,
    Description = @Description,
    Body = @Body,
    Picture = @Picture,
    CreatedById = @CreatedById,
    CreatedBy = @CreatedBy,
    ReplyTo = @ReplyTo,
    IsActive = @IsActive,
    SortIndex = @SortIndex,
    FileAttachmentId = @FileAttachmentId,
    DateCreated = @DateCreated,
    IsDelete = @IsDelete
    WHERE SubjectId = @SubjectId
END
GO

CREATE PROCEDURE Subject_Delete
    @SubjectId int
AS
BEGIN
    DELETE FROM Subject where SubjectId = @SubjectId
END
GO

CREATE PROCEDURE Subject_Paging
    @PageSize int, 
    @PageNumber int
    AS 
    declare @RowStart int
    declare @RowEnd int
    if @PageNumber > 0 
    BEGIN	
    set @PageNumber = @PageNumber - 1
    set @RowStart = @PageSize * @PageNumber + 1;
    set @RowEnd = @RowStart  + @PageSize - 1;
    SELECT TotalRowCount = COUNT(SubjectId) FROM Subject;
    WITH MyTable as 
    (
    SELECT * ,  ROW_NUMBER() OVER (order by SortIndex) as RowNumber From  Subject
    )
    SELECT * from MyTable where RowNumber >= @RowStart and RowNumber <= @RowEnd
END
GO

CREATE proc Subject_SetActive
@SubjectId int
AS
declare @Type bit
SELECT @Type =  IsActive from Subject where SubjectId = @SubjectId
BEGIN
IF(@Type = 1)
Update Subject set IsActive = 0 where SubjectId = @SubjectId
else
Update Subject set IsActive = 1 where SubjectId = @SubjectId
END
GO

CREATE proc Subject_SetSortIndex
@SubjectId int, 
@Type int
AS
declare @CurrentSort int
select @CurrentSort = SortIndex from  Subject where SubjectId = @SubjectId
BEGIN
IF(@Type = 1)
Update Subject set SortIndex =  @CurrentSort + 1 where SubjectId = @SubjectId
else
Update Subject set SortIndex =  @CurrentSort - 1 where SubjectId = @SubjectId
END
GO

CREATE PROCEDURE Subject_RemoveSelectedItem
    @SubjectId int
AS
BEGIN
    UPDATE Subject SET IsDeleted = 1 WHERE SubjectId = @SubjectId
END
GO

CREATE PROCEDURE Subject_RestoreSelectedItem
    @SubjectId int
AS
BEGIN
    UPDATE Subject SET IsDeleted = 0 WHERE SubjectId = @SubjectId
END
GO

CREATE PROCEDURE Subject_RemoveAll
    @SubjectId int
AS
BEGIN
    UPDATE Subject SET IsDeleted = 1
END
GO

CREATE PROCEDURE Subject_RestoreAll
    @SubjectId int
AS
BEGIN
    UPDATE Subject SET IsDeleted = 0
END
GO

